import pandas as pd
import xlrd
import os

import warnings
warnings.filterwarnings("ignore") # ignore some little warnings

def import_df(file_name):
    #print(file_name)
    import_data = pd.read_excel(file_name)# import the xls file
    #create the dataframe
    data_df = pd.DataFrame({
        "img": import_data["图片链接"],
        "price": import_data["价格"],
        "tag": import_data["标签"],
        "m_name": import_data["品牌参数&描述"],
        "s_name": import_data["店铺"],
        "resource": import_data["货源"],
        "m_id": import_data["商品id"],
        "s_id": import_data["商铺id"]
    })
    #print(data_df)
    return data_df

def drop_na(data_df):# drop the none value
    dfna = data_df.dropna(subset=["img", "price", "m_name", "s_name", "m_id", "s_id"])# drop the none value from these columns
    drop_na_df = dfna.drop(index=dfna[dfna['img']=="ne"].index)# some img value is "ne". That is not the value we want
    #print(drop_na_df)
    return drop_na_df

def drop_repeat(df):# drop the repeat items with same m_id
    df = df.drop_duplicates(["m_id"])
    #print(df)
    return df

def insert_tag(df, file_name): # use the search name to create the tag for each good
    tag_value = file_name[2:-4:1] # get the search name from filename
    df["tag"] = df["tag"].map(lambda x: tag_value) # use lambda to change the tag value
    #print(df["tag"])
    return df

def start_dataprocess(file):# call this function and the python will do the all process

    df1 = import_df(file)
    df2 = drop_na(df1)
    df3 = drop_repeat(df2)
    df4 = insert_tag(df3, file)
    #print(df4)

    return df4

def search_path_file(dir):
    file_name_list = []
    for root_dir, sub_dir, files in os.walk(dir):
        #print(root_dir)
        #print(sub_dir)
        #print(files)
        for file in files:
            if file.endswith('xls') :  # judge whether the name suffix is correct
                #file_name_list.append(file)
                #print(file)

                # Building an absolute path
                file_name = os.path.join(root_dir, file)  # xls file from content that this py file exists
                #print(file_name)
                file_name_list.append(file_name)
    return file_name_list

def process_total_df(file_list):# each xls file have their own dataframe. We need to connect them into a big dataframe
    counter = 0
    #df1 = pd.DataFrame()
    for i in range(len(file_list)):
        if(counter == 0): # first dataframe
            df1 = start_dataprocess(file_list[i])
        else: # other dataframe that is need to connect to the dataframe
            df2 = start_dataprocess(file_list[i])
            df1= pd.concat([df1,df2])
        counter+=1
    #print(df1)
    return df1

def data_process_return(abs_path): # just call this function and you can get the whole result

    file_name_list = search_path_file(abs_path)
    result_df = process_total_df(file_name_list)
    #print(result_df)
    return result_df

if __name__ == '__main__':


    data_process_return(r'C:\Users\寒升\PycharmProjects\HelloWorld\DataProcess')
    print(os.getcwd())



